################################################################################
#
# Software Detection based on
# Part Sum Model
#
################################################################################

import pyflag.DB as DB
import pyflag.FlagFramework as FlagFramework
import pyflag.conf
from SoftwareUtil import *

config = pyflag.conf.ConfObject()
config.add_option("PART_SUM_TABLE", default = "PartSum_data")
config.add_option("PART_SUM_RESULT_TABLE", default = "PartSum_result")



class SoftwareDetectionTables(FlagFramework.EventHandler):
    def create(self, dbh, case):
        dbh.execute("""CREATE TABLE IF NOT EXISTS %s (
            `ID` int(32) NOT NULL auto_increment,
            `N` int(32) NOT NULL,
            `ProductCode` int(32) NOT NULL,
            PRIMARY KEY (`ID`),
            KEY product_index (`ProductCode`))""", config.PART_SUM_TABLE)



## Insert into part sum table ProductCode
def insertToPartSumTable(case, productCode, n):
    dbh = DB.DBO(case)
    dbh.insert(config.PART_SUM_TABLE,
        ProductCode = productCode,
        N = n,
        )


## Create model for case
def part_sum_calculate(case):
    dbh = DB.DBO(case)
    dbh.execute("""SELECT SHA1, MD5, N FROM %s WHERE N > 0""", config.SOFT_SCANNER_HASHES)
    row = dbh.fetch()

    ## Process all scan hashes
    while row:
        sha1 = row['SHA1']; md5 = row['MD5'];
        n = float(row['N'])
        productCodeList = getProductCodeList(sha1, md5)

    	for productCode in productCodeList:
            insertToPartSumTable(case, productCode, n)

        ## Get next row
        row = dbh.fetch()

    ## Create result table - PartSum
    dbh.execute("""CREATE TABLE %s AS (SELECT DISTINCT
	    A.ProductCode, 
	    C.ProductName,
	    C.ProductVersion,
	    A.Count, 
	    B.NSRLCount, 
	    round(A.Count / B.NSRLCount * 100, 2) as Proc, 
	    A.Min as NMin, 
	    round(A.LIN_15_sum / B.NSRL_LIN_15, 2) as LIN_15,
    	    round(A.LIN_10_sum / B.NSRL_LIN_10, 2) as LIN_10,
      	    round(A.LIN_5_sum / B.NSRL_LIN_5, 2) as LIN_5,
	    round(A.NormalSum / B.NSRLNormal, 2) as NormalMembership, 
	    round(A.ExtraSum / B.NSRLExtra, 2) as ExtraMembership, 
	    round(A.UltraSum / B.NSRLUltra, 2) as UltraMembership
 	FROM (SELECT
    	    ProductCode,
    	    count(*) as Count,
    	    min(N) as Min, 
    	    sum((sign((16 - N) / 15) + 1) * (16 - N) / 30) AS LIN_15_sum,
    	    sum((sign((11 - N) / 10) + 1) * (11 - N) / 20) AS LIN_10_sum,
    	    sum((sign((6 - N) / 5) + 1) * (6 - N) / 10) AS LIN_5_sum,
    	    sum(1 / N) AS NormalSum,
    	    sum(power(1 / N,  2)) AS ExtraSum,
    	    sum(power(1 / N, 3)) AS UltraSum FROM %s GROUP BY ProductCode) A
	INNER JOIN 
	    %s.%s B ON A.ProductCode = B.ProductCode
	INNER JOIN
	    %s.%s C ON B.ProductCode = C.ProductCode ORDER BY ExtraMembership)""",
    (config.PART_SUM_RESULT_TABLE, config.PART_SUM_TABLE, config.NSRL_DB,
     config.NSRL_PRODUCT_COUNT, config.NSRL_DB, config.NSRL_PRODUCTS)
    )
